import xlrd
import xlwt
import os
from tqdm import tqdm


def ReadExcelFile(excelFilePath, goaltabletitle):
    data = xlrd.open_workbook(excelFilePath)
    sheetNameList = data.sheet_names()
    sheetName = sheetNameList[0]
    regionalizationCode = sheetName[:6]
    table = data.sheet_by_name(sheetName)
    # rowcount = table.nrows
    # colcount = table.ncols  # 列
    tabletitleList = table.row_values(0)
    goalTableTitleColIndex = None
    for listIndex, tabeltitle in enumerate(tabletitleList):
        if goaltabletitle == tabeltitle:
            goalTableTitleColIndex = listIndex
    goalList = table.col_values(goalTableTitleColIndex, start_rowx=1, end_rowx=None)
    newGoalList = []
    for listIndex, goalItem in enumerate(goalList):
        if goalItem[:6] == regionalizationCode:
            singlerow = table.row_values(listIndex + 1, start_colx=0, end_colx=None)
            newGoalList.append(singlerow)
    newGoalList.insert(0, tabletitleList)
    return newGoalList


def ReadExcelTableTitleNameList(excelFilePath, tableIndex=None):
    """
    获取xls文件的表头名称列表
    :param excelFilePath:
    :param tableIndex:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    if tableIndex is None:
        table = data.sheet_by_index(0)
    else:
        table = data.sheet_by_index(tableIndex)
    tableTitleNameList = table.row_values(0)
    del data
    return tableTitleNameList


def ReadExcelTableNameList(excelFilePath):
    """
    获取表名名称
    :param excelFilePath:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    tableNameList = data.sheet_names()
    del data
    return tableNameList


def ReadExcelTableDataList(excelFilePath, tableIndex=None):
    """
    获取slx文件指定表的所有数据
    :param excelFilePath:
    :param tableIndex:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    if tableIndex is None:
        table = data.sheet_by_index(0)
    else:
        table = data.sheet_by_index(tableIndex)
    tableDataList = []
    rowCount = table.nrows
    for rowIndex in range(rowCount):
        singleLineData = table.row_values(rowIndex)
        tableDataList.append(singleLineData)
    del data
    return tableDataList


def DataListByTitleName(dataList, selectNameList):
    dataTitleNameList = dataList[0]
    outDataList = []
    outIndexList = []
    for titleName in selectNameList:
        for listIndex, dataTitleName in enumerate(dataTitleNameList):
            if titleName == dataTitleName:
                outIndexList.append(listIndex)
    for line in dataList[1:]:
        outLine = []
        for index in outIndexList:
            outLine.append(line[index])
        outDataList.append(outLine)
    return outDataList


def ReadExcelFileByTitleNameList(excelFilePath, goalTableTitleNameList):
    """
    获取指定表头的列数据
    :param excelFilePath:
    :param goalTableTitleNameList:
    :return:
    """
    data = xlrd.open_workbook(excelFilePath)
    sheetNameList = data.sheet_names()
    sheetName = sheetNameList[0]
    table = data.sheet_by_name(sheetName)
    tabletitleList = table.row_values(0)

    rowCount = table.nrows
    goalTableTitleColIndex = None
    newcolIndeList = []
    for listIndex, tabeltitle in enumerate(tabletitleList):
        if tabeltitle in goaltabletitleNameList:
            goalTableTitleColIndex = listIndex
            newcolIndeList.append(listIndex)
    multiplecoldataList = []
    for rowIndex in range(rowCount)[1:]:
        table.row_values(rowIndex, start_colx=newcolIndeList, )
    # for cloIndex in newcolIndeList:
    #     goalList = table.col_values(cloIndex, start_rowx=1, end_rowx=None)
    #     table.row_values()
    #     multiplecoldataList.append(goalList)
    return multiplecoldataList


def WriteExcelFile(excelFilePath, data, sheetName="sheet"):
    excelFile = xlwt.Workbook()
    sheet = excelFile.add_sheet(sheetName, cell_overwrite_ok=True)
    style = xlwt.XFStyle()  # 创建样式
    align = xlwt.Alignment()  # 创建对齐方式
    align.horz = 2  # 1-->左对齐，2-->居中，3-->右对齐
    style.alignment = align
    for rowIndex, singlerow in enumerate(data):
        for colIndex, singlecol in enumerate(singlerow):
            sheet.write(rowIndex, colIndex, singlecol, style=style)
    excelFile.save(excelFilePath)
    return


def WriteExcelFileByTitleNameList(excelFilePath, data, sheetName="sheet"):
    excelFile = xlwt.Workbook()
    sheet = excelFile.add_sheet(sheetName, cell_overwrite_ok=True)
    style = xlwt.XFStyle()  # 创建样式
    align = xlwt.Alignment()  # 创建对齐方式
    align.horz = 2  # 1-->左对齐，2-->居中，3-->右对齐
    style.alignment = align
    for rowIndex, singlerow in enumerate(data):
        for colIndex, singlecol in enumerate(singlerow):
            sheet.write(rowIndex, colIndex, singlecol, style=style)
    excelFile.save(excelFilePath)
    return


def task_1():
    sheetName = r"sheet"
    sourceExcelFolderPath = input("请输入需要处理xls文件的文件夹路径：\n")
    # saveExcelFilePath = input("请输入要保存xls文件路径：\n")
    folderPath = os.path.split(sourceExcelFolderPath)[0]
    saveExcelFilePath = os.path.join(folderPath, "ALLData.xls")
    excelFileNameList = os.listdir(sourceExcelFolderPath)
    alldata = []
    for listIndex, excelFileName in enumerate(excelFileNameList):
        excelFilePath = os.path.join(sourceExcelFolderPath, excelFileName)
        if listIndex == 0:
            data = ReadExcelFile(excelFilePath, goaltabletitle)
            alldata = alldata + data
        else:
            data = ReadExcelFile(excelFilePath, goaltabletitle)[1:]
            alldata = alldata + data
    WriteExcelFile(saveExcelFilePath, alldata)
    print("结果文件保存路径：\n", saveExcelFilePath)
    return


def task_2():
    # sourceExcelFolderPath = input("请输入需要处理xls文件的文件夹路径：\n")
    goaltabletitleList = ["乡镇代码", "村代码", "村名称", "村解译面积（亩）"]
    sourceExcelFolderPath = r"E:\王成博\新建文件夹"
    sheetName = r"sheet"
    folderPath = os.path.split(sourceExcelFolderPath)[0]
    saveExcelFilePath = os.path.join(folderPath, "ALLData.xls")
    excelFileNameList = os.listdir(sourceExcelFolderPath)
    alldata = []
    for listIndex, excelFileName in enumerate(excelFileNameList):
        excelFilePath = os.path.join(sourceExcelFolderPath, excelFileName)
        if listIndex == 0:
            multiplecoldataList = ReadExcelFileByTitleNameList(excelFilePath, goaltabletitleList)
            alldata = alldata + multiplecoldataList
        else:
            multiplecoldataList = ReadExcelFile(excelFilePath, goaltabletitle)[1:]
            for list_Index, alldata_item in enumerate(alldata):
                alldata[list_Index] = multiplecoldataList[list_Index] + alldata_item

    WriteExcelFileByTitleNameList(saveExcelFilePath, alldata)
    print("结果文件保存路径：\n", saveExcelFilePath)
    return


def task_3():
    excelFolderPath = input("请输入原始文件存放文件夹路径:\n")
    saveExcelFolderPath = input("请输入保存路径:\n")
    saveExcelFilePath = os.path.join(saveExcelFolderPath, "AllData.xls")
    excelFileNameList = os.listdir(excelFolderPath)
    outDataList = []
    selectNameList = ["乡镇代码", "村代码", "村名称", "村解译面积（亩）"]
    for excelFileName in tqdm(excelFileNameList):
        excelFilePath = os.path.join(excelFolderPath, excelFileName)
        print(excelFilePath)
        dataList = ReadExcelTableDataList(excelFilePath)
        data = DataListByTitleName(dataList, selectNameList)
        outDataList = outDataList + data
    outDataList.insert(0, selectNameList)
    WriteExcelFileByTitleNameList(saveExcelFilePath, outDataList)
    print("end!")
    return


def main():
    task_3()
    return


if __name__ == "__main__":
    main()